﻿Imports System.Data.SqlClient
Public Class dbMasterTable
    Dim Var As New varDeclare

    Public Function getTypeStockList() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT * FROM item_TipeStock"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function TypeStockManagement(Type As String, ID As String, Name As String, isStock As String, color As String, EntryBy As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_TypeStockManagement"
        command.Parameters.Add("@Type", SqlDbType.VarChar).Value = Type
        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID
        command.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name
        command.Parameters.Add("@isStock", SqlDbType.VarChar).Value = isStock
        command.Parameters.Add("@color", SqlDbType.VarChar).Value = color
        command.Parameters.Add("@EntryBy", SqlDbType.VarChar).Value = EntryBy
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return (result).ToString()
    End Function

    Public Function getGroupItemList() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT GROUPS 'Groups ID', GROUP_DESCRIPTION 'Group Desc', GROUP_STATUS_CODE 'Status' FROM GROUP_ITEM WITH(NOLOCK) WHERE COMP = @CompID"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GroupsItemManagement(Type As String, ID As String, Desc As String, Status As String, EntryBy As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GroupsItemManagement"
        command.Parameters.Add("@Type", SqlDbType.VarChar).Value = Type
        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = ID
        command.Parameters.Add("@Desc", SqlDbType.VarChar).Value = Desc
        command.Parameters.Add("@Status", SqlDbType.VarChar).Value = Status
        command.Parameters.Add("@EntryBy", SqlDbType.VarChar).Value = EntryBy
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return (result).ToString()
    End Function

    Public Function getItemCodeList() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT IC.GROUPS 'Groups', IC.Code 'Item Code', IC.IC_DESCRIPTION 'Item Desc',IC. STATUS 'Status' FROM ITEM_CODE IC WITH(NOLOCK) INNER JOIN GROUP_ITEM GI WITH(NOLOCK) ON GI.GROUPS = IC.GROUPS WHERE ISNULL(IC.COMP, @CompID) = @CompID"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function ItemCodeManagement(Type As String, Groups As String, ItemCode As String, ItemDesc As String, Status As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_ItemCodeManagement"
        command.Parameters.Add("@Type", SqlDbType.VarChar).Value = Type
        command.Parameters.Add("@Groups", SqlDbType.VarChar).Value = Groups
        command.Parameters.Add("@ID", SqlDbType.VarChar).Value = ItemCode
        command.Parameters.Add("@Desc", SqlDbType.VarChar).Value = ItemDesc
        command.Parameters.Add("@Status", SqlDbType.VarChar).Value = Status
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return (result).ToString()
    End Function

    Public Function GetPrincipalList() As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT * FROM vPrincipal"
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetJournalCodeList() As DataTable
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT GLTR_JURNAL_CODE 'GLJournalID',GLTR_JURN_DESC 'GLJournalName' From GL_TRANS_HEADER WITH(NOLOCK) WHERE ISNULL(GLTR_COMP_CODE,@CompID) = @CompID"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetJournalYearList() As DataTable
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT DISTINCT CAST(YEAR(GLTR_DATE) AS CHAR(4)) 'Year' FROM GL_TRANS WITH (NOLOCK) WHERE ISNULL(GLTR_COMP_CODE,@CompID) = @CompID"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds.Tables(0)
    End Function

    Public Function GetJournalPeriodList(Year As String) As DataTable
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT DISTINCT (CAST(YEAR(GLTR_DATE) AS CHAR(4)) + '-' + RIGHT(CAST(MONTH(GLTR_DATE) + 100 AS VARCHAR), 2)) 'Period' FROM GL_TRANS WITH (NOLOCK) WHERE ISNULL(GLTR_COMP_CODE,@CompID) = @CompID AND CAST(YEAR(GLTR_DATE) AS CHAR(4)) = ISNULL(@Year,CAST(YEAR(GLTR_DATE) AS CHAR(4)))"
        If (Year <> "") Then
            command.Parameters.Add("@Year", SqlDbType.VarChar).Value = Year
        Else
            command.Parameters.Add("@Year", SqlDbType.VarChar).Value = DBNull.Value
        End If
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds.Tables(0)
    End Function
End Class
